Constraints on Publishing Data Service Objects to SQL
There are some semantic and structural constraints to publishing data service objects to SQL.
Semantic constraints include some general types of objects as private functions.
See Mapping Rules for a matrix showing publishable Oracle Data Service Integrator object types and their corresponding SQL object types.
The following table outlines the structural constraints on publishing data service artifacts to SQL.
Limitation
Discussion
Limitation affecting all SQL objects
Limitations in this section affect publication to any type of SQLobject.
Functions referring to types that are neither simple nor elements
Examples of such types include item, node, and attribute.
Functions with simple types that have no corresponding SQL type
The simple type on the XQuery side must correspond with a JDBC-supported SQL type, such as QName for example.
Functions with anonymous element types
Functions containing elements where the name is not defined are not mapable. For example:
declare function f() as element()
Functions declarations using recursive XML types
For example, a function declaration with a complex type (PersonType) containing an element that is also of type PersonType is not mapable, as shown by the following:
Limitations affecting publishing as a SQL Function
Limitations in this category affect publishing as a SQL functions
Function with a sequence parameter type and an arity greater than 1.
An example shows xs:int* as the sequence parameter type:
declare function f($p as xs:int*, $q as xs:string) as xs:int
Functions with element types
declare function f ($p as element(e)) as xs:int
How Non-Tabular Element Types Affect the Ability to Publish Functions as SQL Objects
The structure of a data service function determines whether it can be mapped to an SQL object or not. For example, a parameterized function cannot be published as an SQL table since by definition SQL tables do not take parameters. Some structural constraints are practically self-evident; others are less obvious.
A quick way to determine if a particular function can be published to a particular type of SQL object is to drag the function to a SQL object table, stored procedure, or functions folder. Even if the function is grayed out — meaning that it cannot be published to any type of SQL object — an alert dialog will appear explaining why the selected object cannot be published.
For example, functions with non-tabular element types cannot be published as tables or stored procedures because XML output structure cannot be mapped to a normalized SQL table.
Underlying each data service is an XML type, or schema. Some XML types are readily mapped for JDBC use because they are — like SQL tables — two dimensional.
is publishable as a table in the following form as long as there is one or fewer customer orders
associated with the customer:
FIRST_NAME
LAST_NAME
CUSTOMER_ID
ORDER_ID
C_ID
ORDER_DT
Jack
Black
CUSTOMER1
ORDER_1_0
CUSTOMER1
2001-10-01
If, however, the CUSTOMER_ORDER type is unbounded, meaning that it can represent more than one order associated with a single customer, the structure no longer corresponds to a well-formed relational table and the mapping is not allowed.